package com.mobiusws.util;


import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil extends ExportingUtil {
	
	String strSheetName;
	Workbook wb=null;
	
	public Workbook getWb()
	{
		return wb;
	}

	public ExcelUtil(String strSheetName)
	{
		super();
		this.strSheetName = strSheetName;
	}

	public static String ConvertDate(String strDate) {
		
		String strRe = null;
		
		int ilen = strDate.length();
		if(ilen==8) //day
		{
			SimpleDateFormat inFormat = new SimpleDateFormat("yyyyMMdd");
			SimpleDateFormat outFormat = new SimpleDateFormat("yyyy-MM-dd");
			
			try {
				Date inDate = inFormat.parse(strDate);
				
				strRe = outFormat.format(inDate);
			} catch (ParseException e) {
				e.printStackTrace();
			}
			
			
		}
		else if(ilen>8 && ilen<=12)
		{
			SimpleDateFormat inFormat = new SimpleDateFormat("yyyyMMddHH");
			SimpleDateFormat outFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			try {
				Date inDate = inFormat.parse(strDate);
				
				strRe = outFormat.format(inDate);
			} catch (ParseException e) {
				e.printStackTrace();
			}
		} else if(ilen == 14) {
			SimpleDateFormat inFormat = new SimpleDateFormat("yyyyMMddHHmmss");
			SimpleDateFormat outFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			try {
				Date inDate = inFormat.parse(strDate);
				
				strRe = outFormat.format(inDate);
			} catch (ParseException e) {
				e.printStackTrace();
			}
		} else {
			strRe = strDate;
		}
			
		return strRe;
	}

	public static boolean AddLogo(Workbook wb, String strSheetName, int iRow, int iLen) {
		boolean bSuccess = false;

		try {
			Sheet sheet = wb.getSheet(strSheetName);
			
			CreationHelper createHelper = wb.getCreationHelper();
			
			Row row = sheet.createRow(iRow);
			row.setHeightInPoints(45);

			// get the logo data to the workbook
			InputStream is = new FileInputStream("/opt/GK2/portal/resource/logo.png");
			byte[] bytes = IOUtils.toByteArray(is);
			int picIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
			is.close();

			// Create the drawing patriarch. This is the top level container for
			// all shapes.
			Drawing drawing = sheet.createDrawingPatriarch();

			// add a picture shape
			ClientAnchor anchor = createHelper.createClientAnchor();
			anchor.setAnchorType(3);

			// set top-left corner of the picture,
			// subsequent call of Picture#resize() will operate relative to it
			anchor.setRow1(iRow);
			anchor.setCol1(2);
			Picture pict = drawing.createPicture(anchor, picIdx);

			// auto-size picture relative to its top-left corner
			pict.resize();
			
			//merge the sheet
		    sheet.addMergedRegion(new CellRangeAddress(  iRow, iRow, 0, iLen-1)); //Merge the column from 0 to 2, three columns


			bSuccess = true;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		return bSuccess;
	}

	public static boolean AddTitle(Workbook wb, String strSheetName, int iRow, ExportReport reportStruct) {
		
		boolean bSuccess=false;
		
		Sheet sheet = wb.getSheet(strSheetName);
		
		Font fBold = wb.createFont();
		fBold.setBoldweight(Font.BOLDWEIGHT_BOLD);
		CellStyle csBold = wb.createCellStyle();
		csBold.setFont(fBold);

		Font fWhite = wb.createFont();
		// fWhite.setFontHeightInPoints(24);
		fWhite.setColor(IndexedColors.WHITE.getIndex());
		CellStyle csBlueBK = wb.createCellStyle();
		csBlueBK.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
		csBlueBK.setFillPattern(CellStyle.SOLID_FOREGROUND);
		csBlueBK.setFont(fWhite);

		int iCurRow = iRow;
		// write the report name
		Row row = sheet.createRow(iCurRow);
		Cell cell = row.createCell(0);
		cell.setCellStyle(csBold);
		cell.setCellValue("User:" + reportStruct.getReportName() );

		//merge the sheet
		int iColumnNum = reportStruct.getTableColumnsArray().length;
	    sheet.addMergedRegion(new CellRangeAddress(  iCurRow, iCurRow, 0, iColumnNum-1)); //Merge the column from 0 to 2, three columns

	    
		// write the date or date/hour
		String strDate = ConvertDate(reportStruct.getReportDate());
		if(strDate==null)  strDate=reportStruct.getReportDate(); // do nothing
		strDate = "Extraction time:" + strDate;
		
		iCurRow++;
		row = sheet.createRow(iCurRow);
		cell = row.createCell(0);
		cell.setCellStyle(csBold);
		cell.setCellValue(strDate);
		
		//merge the sheet
	    sheet.addMergedRegion(new CellRangeAddress(  iCurRow, iCurRow, 0, iColumnNum-1)); //Merge the column from 0 to 2, three columns
		
		//add by wilson
		iCurRow++;
		row = sheet.createRow(iCurRow);
		cell = row.createCell(0);
		cell.setCellStyle(csBold);
		cell.setCellValue("Criteria:"+reportStruct.getQueryCriteria());
		//add by wilson

		//merge the sheet
	    sheet.addMergedRegion(new CellRangeAddress(  iCurRow, iCurRow, 0, iColumnNum-1)); //Merge the column from 0 to 2, three columns

		// write the titles of the columns
		iCurRow++;
		row = sheet.createRow(iCurRow);
		for (int i = 0; i < reportStruct.getReportTitleArray().length; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(csBlueBK);
			cell.setCellValue(reportStruct.getReportTitleArray()[i]);
		}
		
		bSuccess=true;
		
		return bSuccess;

	}

	public static boolean AddTitleW(Workbook wb, String strSheetName, int iRow, ExportReport reportStruct) {
		
		boolean bSuccess=false;
		
		Sheet sheet = wb.getSheet(strSheetName);
		
		Font fBold = wb.createFont();
		fBold.setBoldweight(Font.BOLDWEIGHT_BOLD);
		CellStyle csBold = wb.createCellStyle();
		csBold.setFont(fBold);

		Font fWhite = wb.createFont();
		// fWhite.setFontHeightInPoints(24);
		fWhite.setColor(IndexedColors.WHITE.getIndex());
		CellStyle csBlueBK = wb.createCellStyle();
		csBlueBK.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
		csBlueBK.setFillPattern(CellStyle.SOLID_FOREGROUND);
		csBlueBK.setFont(fWhite);

		int iCurRow = iRow;
		// write the report name
		Row row = sheet.createRow(iCurRow);
		Cell cell = row.createCell(0);
		cell.setCellStyle(csBold);
		cell.setCellValue(reportStruct.getReportName() );

		//merge the sheet
		int iColumnNum = reportStruct.getTableColumnsArray().length;
	    sheet.addMergedRegion(new CellRangeAddress(  iCurRow, iCurRow, 0, iColumnNum-1)); //Merge the column from 0 to 2, three columns

	    
		// write the date or date/hour
		String strDate = ConvertDate(reportStruct.getReportDate());
		if(strDate==null)  strDate=reportStruct.getReportDate(); // do nothing
		
		iCurRow++;
		row = sheet.createRow(iCurRow);
		cell = row.createCell(0);
		cell.setCellStyle(csBold);
		cell.setCellValue(strDate);
		
		//merge the sheet
	    sheet.addMergedRegion(new CellRangeAddress(  iCurRow, iCurRow, 0, iColumnNum-1)); //Merge the column from 0 to 2, three columns
	

		// write the titles of the columns
		iCurRow++;
		row = sheet.createRow(iCurRow);
		for (int i = 0; i < reportStruct.getReportTitleArray().length; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(csBlueBK);
			cell.setCellValue(reportStruct.getReportTitleArray()[i]);
		}
		
		bSuccess=true;
		
		return bSuccess;

	}


	public boolean AddBody(Workbook wb, String strSheetName, int iRow, ResultSet rs, ExportReport reportStruct, String strCountryCode, long maskBits, int iLen) {
		
		boolean bSuccess=false;
		
		Sheet sheet = wb.getSheet(strSheetName);
		
		Font fBlack = wb.createFont();
		// fWhite.setFontHeightInPoints(24);
		fBlack.setColor(IndexedColors.BLACK.getIndex());
		CellStyle csBlueBK = wb.createCellStyle();
		// csBlueBK.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
		// csBlueBK.setFillPattern(CellStyle.SOLID_FOREGROUND);
		csBlueBK.setFont(fBlack);
		
		Font fRed = wb.createFont();// Define red font cell style
		fRed.setColor(IndexedColors.RED.getIndex());
		CellStyle csRedBK = wb.createCellStyle();
		csRedBK.setFont(fRed);

		int iCurRow = iRow;
		Row row;
		Cell cell;
		String msisdn =  "";
		try {
			while(rs.next())
			{
				row = sheet.createRow(iCurRow);
				
				for (int i = 0; i < iLen; i++) 
				{
					cell = row.createCell(i);
					cell.setCellStyle(csBlueBK);

					if(reportStruct.getTableColumnsArray()[i].indexOf("odds")>=0 || reportStruct.getTableColumnsArray()[i].indexOf("off_peak")>=0) //percentage
					{
						cell.setCellValue(rs.getDouble(i + 1));
						
						csBlueBK.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
						cell.setCellStyle(csBlueBK);
					}
					else if(reportStruct.getTableColumnsArray()[i].indexOf("msisdn")>=0 && !reportStruct.getTableColumnsArray()[i].startsWith("msisdn_f"))  
					{
						msisdn = rs.getString(i + 1);
						String[] strArray = ExportingUtil.getStringAndFlagArray(msisdn, reportStruct);
						msisdn = strArray[0];
						String msisdn_f = strArray[1];
						
						if(maskBits>0) //unmask the msisdn
						{
							msisdn = intXORMask(msisdn, maskBits);
						}
						if ("1".equals(msisdn_f)) {
							cell.setCellStyle(csRedBK);// Set the color of font to red.
						}
						cell.setCellValue(msisdn);
					}
					else if("imei".equals(reportStruct.getTableColumnsArray()[i].trim()))
					{
						String[] strArray = ExportingUtil.getStringAndFlagArray(rs.getString(i + 1), reportStruct);
						String imei = strArray[0];
						String imei_f = strArray[1];
						if ("1".equals(imei_f)) {
							cell.setCellStyle(csRedBK);
						}
						cell.setCellValue(imei);
					}
					else if("imsi".equals(reportStruct.getTableColumnsArray()[i].trim()))
					{
						String[] strArray = ExportingUtil.getStringAndFlagArray(rs.getString(i + 1), reportStruct);
						String imsi = strArray[0];
						String imsi_f = strArray[1];
						if ("1".equals(imsi_f)) {
							cell.setCellStyle(csRedBK);
						}
						cell.setCellValue(imsi);
					}
					else if("cell_id".equals(reportStruct.getTableColumnsArray()[i].trim()))
					{
						String[] strArray = ExportingUtil.getStringAndFlagArray(rs.getString(i + 1), reportStruct);
						String cell_id = strArray[0];
						String cell_id_f = strArray[1];
						if ("1".equals(cell_id_f)) {
							cell.setCellStyle(csRedBK);
						}
						cell.setCellValue(cell_id);
					}
					else
					{
						cell.setCellValue(rs.getString(i + 1));
					}
				}
				iCurRow++;
			}
			bSuccess=true;
			
		} catch (Exception e) {
			e.printStackTrace();
		}

		return bSuccess;

	}

	public static void AdjustAutoWidth(Workbook wb, String strSheetName, int iLen)
	{
		Sheet sheet = wb.getSheet(strSheetName);
		
		for(int i=0; i<iLen; i++)
		{
			sheet.autoSizeColumn(i);
		}
	}
	
	public void AdjustAutoWidth(int iLen)
	{
		AdjustAutoWidth(wb, strSheetName, iLen);
	}
	
	public static boolean AddBody(Workbook wb, String strSheetName, int iRow, Vector<String> vtData, int iLen) {
		
		boolean bSuccess=false;
		
		Sheet sheet = wb.getSheet(strSheetName);
		
		Font fBlack = wb.createFont();
		// fWhite.setFontHeightInPoints(24);
		fBlack.setColor(IndexedColors.BLACK.getIndex());
		CellStyle csBlueBK = wb.createCellStyle();
		// csBlueBK.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
		// csBlueBK.setFillPattern(CellStyle.SOLID_FOREGROUND);
		csBlueBK.setFont(fBlack);

		int iCurRow = iRow;
		Row row;
		Cell cell;

		int iTotal = vtData.size(), iPos=0;
		
			while(iTotal>=0)
			{
				row = sheet.createRow(iCurRow);
				
				for (int i = iPos; i < iPos+iLen; i++) 
				{
					cell = row.createCell(i);
					cell.setCellStyle(csBlueBK);
					cell.setCellValue(vtData.get(i));
					iTotal--;
				}
				iPos = iPos+iLen;
				
				iCurRow++;
			}
			bSuccess=true;
			
		return bSuccess;

	}
	
	public static Workbook CreateExcelSheet(String strSheetName)
	{
		Workbook wb = new XSSFWorkbook();
		
		wb.createSheet(strSheetName);
		
		return wb;
	}
	
	public static boolean SaveExcelSheet(String strFilePath, Workbook wb)
	{
		boolean bSuccess=false;
		
		try {
			FileOutputStream fileOut = new FileOutputStream(strFilePath);
			BufferedOutputStream Buff =  new  BufferedOutputStream(fileOut);

			wb.write(Buff);
			Buff.flush();
			Buff.close();
			fileOut.close();
			wb.removeSheetAt(0);
			bSuccess=true;
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return bSuccess;
	}
	
/*Internal method*/
	public boolean CreateExcelSheet() {
		boolean bSuccess = false;

		if(wb==null)
			wb = CreateExcelSheet(strSheetName);

		if (wb != null) {

			bSuccess = true;
		}

		return bSuccess;
	}
	
	public boolean SaveExcelSheet(String strFilePath)
	{
		return SaveExcelSheet(strFilePath,wb);
	}
	
	public boolean AddLogo(int iRow, int iLen)
	{
		return AddLogo(wb, strSheetName, iRow, iLen);
	}
	
	public boolean AddTitle(int iRow, ExportReport reportStruct)
	{
		return AddTitle(wb, strSheetName, iRow, reportStruct);
	}
	
	public boolean AddTitleW(int iRow, ExportReport reportStruct)
	{
		return AddTitleW(wb, strSheetName, iRow, reportStruct);
	}
	
	public boolean AddBody(int iRow, ResultSet rs, ExportReport reportStruct, String strCountryCode, long maskBits, int iLen)
	{
		return AddBody(wb, strSheetName, iRow, rs, reportStruct, strCountryCode, maskBits, iLen);
	}
	
	public boolean AddBody(int iRow, List<List<Object>> ls, ExportReport reportStruct, String strCountryCode, long maskBits, int iLen)
	{
		return AddBody(wb, strSheetName, iRow, ls, reportStruct, strCountryCode, maskBits, iLen);
	}
	
	public boolean AddBody(Workbook wb, String strSheetName, int iRow, List<List<Object>> ls, ExportReport reportStruct, String strCountryCode, long maskBits, int iLen) {
		
		boolean bSuccess=false;
		
		Sheet sheet = wb.getSheet(strSheetName);
		
		Font fBlack = wb.createFont();
		// fWhite.setFontHeightInPoints(24);
		fBlack.setColor(IndexedColors.BLACK.getIndex());
		CellStyle csBlueBK = wb.createCellStyle();
		// csBlueBK.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
		// csBlueBK.setFillPattern(CellStyle.SOLID_FOREGROUND);
		csBlueBK.setFont(fBlack);
		
		Font fRed = wb.createFont();// Define red font cell style
		fRed.setColor(IndexedColors.RED.getIndex());
		CellStyle csRedBK = wb.createCellStyle();
		csRedBK.setFont(fRed);

		int iCurRow = iRow;
		Row row;
		Cell cell;
		String msisdn =  "";
		List<Object> ob = null;
		try {
			for(int l = 0; l < ls.size(); l++)
			{
				ob = ls.get(l);
				
				row = sheet.createRow(iCurRow);
				
				for (int i = 0; i < iLen; i++) 
				{
					cell = row.createCell(i);
					cell.setCellStyle(csBlueBK);

					if(reportStruct.getTableColumnsArray()[i].indexOf("odds")>=0 || reportStruct.getTableColumnsArray()[i].indexOf("off_peak")>=0) //percentage
					{
						cell.setCellValue(Double.parseDouble(ob.get(i).toString()));
						
						csBlueBK.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
						cell.setCellStyle(csBlueBK);
					}
					else if(reportStruct.getTableColumnsArray()[i].indexOf("msisdn")>=0 && !reportStruct.getTableColumnsArray()[i].startsWith("msisdn_f"))  
					{
						msisdn = ob.get(i).toString();
						String[] strArray = ExportingUtil.getStringAndFlagArray(msisdn, reportStruct);
						msisdn = strArray[0];
						String msisdn_f = strArray[1];
						if(maskBits>0) //unmask the msisdn
						{
							msisdn = intXORMask(msisdn, maskBits);
						}
						
						if ("1".equals(msisdn_f)) {
							cell.setCellStyle(csRedBK);// Set the color of font to red.
						}
						cell.setCellValue(msisdn);
					}
					else if("imei".equals(reportStruct.getTableColumnsArray()[i].trim()))
					{
						String[] strArray = ExportingUtil.getStringAndFlagArray(ob.get(i).toString(), reportStruct);
						String imei = strArray[0];
						String imei_f = strArray[1];
						if ("1".equals(imei_f)) {
							cell.setCellStyle(csRedBK);
						}
						cell.setCellValue(imei);
					}
					else if("imsi".equals(reportStruct.getTableColumnsArray()[i].trim()))
					{
						String[] strArray = ExportingUtil.getStringAndFlagArray(ob.get(i).toString(), reportStruct);
						String imsi = strArray[0];
						String imsi_f = strArray[1];
						if ("1".equals(imsi_f)) {
							cell.setCellStyle(csRedBK);
						}
						cell.setCellValue(imsi);
					}
					else if("cell_id".equals(reportStruct.getTableColumnsArray()[i].trim()))
					{
						String[] strArray = ExportingUtil.getStringAndFlagArray(ob.get(i).toString(), reportStruct);
						String cell_id = strArray[0];
						String cell_id_f = strArray[1];
						if ("1".equals(cell_id_f)) {
							cell.setCellStyle(csRedBK);
						}
						cell.setCellValue(cell_id);
					}
					else
					{
						cell.setCellValue(ob.get(i).toString());
					}
				}
				iCurRow++;
			}
			bSuccess=true;
			
		} catch (Exception e) {
			e.printStackTrace();
		}

		return bSuccess;

	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {

	}

}
